package jmini.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import jmini.annotation.AP_FIELD;
import jmini.annotation.AP_MODEL;
import jmini.dto.Pagination;
import jmini.fw.MiniHelper;
import jmini.modules.Module;
import jmini.utils.Str;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;


/**
 * 封装简单的crud操作的DAO
 * @author Jason Ma
 *
 */
@Repository
public class MiniDAO extends AbstractBaseDAO{
	
	/**
	 * 添加数据
	 * @param obj
	 * @return
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String save(Module model) throws IllegalArgumentException, IllegalAccessException{
		Class rt_class = model.getClass();
		if(rt_class.isAnnotationPresent(AP_MODEL.class)){
			StringBuilder sql_f = new StringBuilder();
			StringBuilder sql_p = new StringBuilder();
			List<Object> args = new ArrayList<Object>();
			AP_MODEL apmodel = (AP_MODEL)rt_class.getAnnotation(AP_MODEL.class);
			if(apmodel.autoIncreaseId() == false){//如果id不是自增长，且id未设置，则设置id
				if(Str.isEmpty(model.getId())){
					model.setId(generateId());
				}
			}
			sql_f.append("INSERT ").append(apmodel.tableName()).append(" ");
			if(apmodel.autoIncreaseId() == true){//如果id为自增长字段，则跳过id字段插入值
				sql_f.append("(CREATE_DATE, CREATE_UID");
				sql_p.append("(?, ?");
				args.add(new Date());
			}else{
				sql_f.append("(ID, CREATE_DATE, CREATE_UID");
				sql_p.append("(?, ?, ?");
				args.add(model.getId());
				args.add(new Date());
			}
			if(Str.isEmpty(model.getCreateUid())){
				args.add(MiniHelper.getOperatorId());
			}else{
				args.add(model.getCreateUid());
			}
			//处理字段
			Field[] fields = rt_class.getDeclaredFields();
			for(Field field : fields){
				if(field.isAnnotationPresent(AP_FIELD.class)){
					AP_FIELD apfield = (AP_FIELD)field.getAnnotation(AP_FIELD.class);
					//仅对creat() == true字段进行创建操作
					if(apfield != null && !Str.isEmpty(apfield.fieldName()) && apfield.create() == true){
						field.setAccessible(true);
						Object fieldvalue = field.get(model);
						sql_f.append(", ").append(apfield.fieldName());
						sql_p.append(", ?");
						args.add(fieldvalue);
					}
				}
			}
			sql_f.append(") ");
			sql_p.append(") ");
			sql_f.append(" VALUES ").append(sql_p);
			if(apmodel.autoIncreaseId() == false){
				jminiJDBCTemplate.update(sql_f.toString(), args.toArray());
				return model.getId();
			}else{
				final String tmpsql = sql_f.toString();
				final Object[] tmpargs = args.toArray();
				KeyHolder keyholder = new GeneratedKeyHolder();
				jminiJDBCTemplate.update(new PreparedStatementCreator() {  
		            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
		            	
		                PreparedStatement ps = connection.prepareStatement(tmpsql, new String[] { "ID" });
		                Object tmpoarg = null;
		                for(int i=1 ; i <= tmpargs.length; i++){
		                	tmpoarg = tmpargs[i];
	                		if(tmpoarg instanceof Date || tmpoarg instanceof Calendar){
	                			ps.setObject(i, tmpoarg, Types.TIMESTAMP);
		                	}else{
		                		ps.setObject(i, tmpoarg);
		                	}
		                }
		                return ps;
		            }  
		        }, keyholder);
				model.setId(String.valueOf(keyholder.getKey().intValue()));
				return model.getId();
			}
			
		}else{
			return null;
		}
		
	}
	
	/**
	 * 删除数据
	 * @param model
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public void delete(Module model) throws IllegalArgumentException, IllegalAccessException{
		Class rt_class = model.getClass();
		if(rt_class.isAnnotationPresent(AP_MODEL.class)){
			StringBuilder sql = new StringBuilder();
			String tableName = null;
			AP_MODEL apmodel = (AP_MODEL)rt_class.getAnnotation(AP_MODEL.class);
			tableName = apmodel.tableName();
			//表名、id不为空时开始删除
			if(!Str.isEmpty(tableName) && !Str.isEmpty(model.getId())){//如果id为空，不删除任何数据
				sql.append("DELETE FROM ").append(tableName).append(" WHERE ID = ?");
				jminiJDBCTemplate.update(sql.toString(), model.getId());
			}
		}
	}
	
	/**
	 * 指定更新操作的字段
	 * @param model
	 * @param fieldsNames
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public void updateFields(Module model, List<String> fieldsNames) throws IllegalArgumentException, IllegalAccessException{
		Class rt_class = model.getClass();
		if(rt_class.isAnnotationPresent(AP_MODEL.class)){
			StringBuilder sql = new StringBuilder();
			List<Object> args = new ArrayList<Object>();
			AP_MODEL apmodel = (AP_MODEL)rt_class.getAnnotation(AP_MODEL.class);
			sql.append("UPDATE ").append(apmodel.tableName()).append(" SET WRITE_DATE = ?, WRITE_UID = ?");
			args.add(new Date());
			if(Str.isEmpty(model.getWriteUid())){
				args.add(MiniHelper.getOperatorId());
			}else{
				args.add(model.getWriteUid());
			}
			Field[] fields = rt_class.getDeclaredFields();
			//处理字段
			for(Field field : fields){
				if(field.isAnnotationPresent(AP_FIELD.class)){
					AP_FIELD apfield = (AP_FIELD)field.getAnnotation(AP_FIELD.class);
					//仅对update() == true字段进行创建操作
					if(apfield != null && !Str.isEmpty(apfield.fieldName()) && apfield.update() == true){
						field.setAccessible(true);
						Object fieldvalue = field.get(model);
						if(fieldsNames != null && fieldsNames.size() > 0){
							if(fieldsNames.contains(apfield.fieldName())){
								sql.append(", ").append(apfield.fieldName()).append(" = ?");
								args.add(fieldvalue);
							}
						}else{
							sql.append(", ").append(apfield.fieldName()).append(" = ?");
							args.add(fieldvalue);
						}
					}
				}
			}
			//id非空，开始进行操作
			if(!Str.isEmpty(model.getId())){
				sql.append(" WHERE ID = ?");
				args.add(model.getId());
				jminiJDBCTemplate.update(sql.toString(), args.toArray());
			}
		}
	}
	
	public <T> T query(String id ,Class<T> requiredType){
		return query(id ,requiredType,true);
	}
	
	/**
	 * 通过id查询模型数据
	 * @param id
	 * @param type
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> T query(String id ,Class<T> requiredType, Boolean isDealOne2Many){
		Class rt_class = requiredType;
		if(rt_class.isAnnotationPresent(AP_MODEL.class)){
			StringBuilder sql = new StringBuilder();
			String tableName = null;
			AP_MODEL apmodel = (AP_MODEL)rt_class.getAnnotation(AP_MODEL.class);
			tableName = apmodel.tableName();
			//表名、id不为空时开始删除
			if(!Str.isEmpty(tableName) && !Str.isEmpty(id)){//如果id为空，不删除任何数据
				sql.append("SELECT * FROM ").append(tableName).append(" WHERE ID = ?");
				List<T> resultes = jminiJDBCTemplate.query(sql.toString(), new ApModelRowMapper(requiredType, this, isDealOne2Many), id);
				if(resultes != null && resultes.size() > 0){
					return resultes.get(0);
				}else{
					return null;
				}
			}else{
				return null;
			}
		}else{
			return null;
		}
	}
	
	public <T> List<T> queryList(String whereStr, String orderStr, Class<T> requiredType, Object... args){
		return queryList(whereStr, orderStr, requiredType, true, args);
	}
	
	/**
	 * 根据条件查询Model数据
	 * @param whereStr
	 * @param orderStr
	 * @param requiredType
	 * @param args
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	<T> List<T> queryList(String whereStr, String orderStr, Class<T> requiredType, Boolean isDealOne2Many, Object... args){
		Class rt_class = requiredType;
		List<T> r = null;
		if(rt_class.isAnnotationPresent(AP_MODEL.class)){
			StringBuilder sql = new StringBuilder();
			AP_MODEL apmodel = (AP_MODEL)rt_class.getAnnotation(AP_MODEL.class);
			String tableName = apmodel.tableName();
			//表名、id不为空时开始删除
			if(!Str.isEmpty(tableName)){//如果tableName不为空
				sql.append("SELECT * FROM ").append(tableName).append(" ");
				if(!Str.isEmpty(whereStr)){
					sql.append("WHERE ").append(whereStr);
				}
				
				if(!Str.isEmpty(orderStr)){
					sql.append(" ORDER BY ").append(orderStr);
				}
				r = jminiJDBCTemplate.query(sql.toString() , args, new ApModelRowMapper(requiredType, this, isDealOne2Many));
				return r;
			}
		}
		return r;
	}
	
	public void queryByPage(Pagination p, String whereStr, String orderStr, Class requiredType, Object... args){
		queryByPage(p, whereStr, orderStr, requiredType, true, args);
	}
	
	/**
	 * 单表分页查询
	 * @param p
	 * @param requiredType
	 * @param whereStr
	 * @param orderStr
	 * @param args
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	void queryByPage(Pagination p, String whereStr, String orderStr, Class requiredType, Boolean isDealOne2Many, Object... args){
		Class rt_class = requiredType;
		if(rt_class.isAnnotationPresent(AP_MODEL.class)){
			StringBuilder sql_count = new StringBuilder();
			StringBuilder sql = new StringBuilder();
			AP_MODEL apmodel = (AP_MODEL)rt_class.getAnnotation(AP_MODEL.class);
			String tableName = apmodel.tableName();
			sql_count.append("SELECT COUNT(ID) FROM ").append(tableName).append(" ");
			sql.append("SELECT * FROM ").append(tableName).append(" ");
			if(!Str.isEmpty(whereStr)){
				sql.append("WHERE ").append(whereStr);
				sql_count.append("WHERE ").append(whereStr);
			}
			Integer count = jminiJDBCTemplate.queryForObject(sql_count.toString(), args, Integer.class);
			p.setMaxRows(count);
			List<Object> argslist = new ArrayList<Object>();
			if(args != null){
				argslist.addAll(Arrays.asList(args));
			}
			argslist.add(p.calculateMaxRecord());
			argslist.add(p.getPageSize());
			if(!Str.isEmpty(orderStr)){
				sql.append(" ORDER BY ").append(orderStr);
			}
			sql.append(" LIMIT ? , ? ");
			List<Module> models = jminiJDBCTemplate.query(sql.toString() , argslist.toArray(), new ApModelRowMapper(requiredType, this, isDealOne2Many));
			p.setDatas(models);
		}
	}
	
	public void queryBySQLPage(Pagination p, String countSQLStr, String sqlStr, String whereStr, String orderStr, Class requiredType, Object... args){
		queryBySQLPage(p, countSQLStr, sqlStr, whereStr, orderStr, requiredType, true, args);
	}
	
	/**
	 * 自定义SQL分页查询
	 * @param p
	 * @param requiredType
	 * @param whereStr
	 * @param orderStr
	 * @param args
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	void queryBySQLPage(Pagination p, String countSQLStr, String sqlStr, String whereStr, String orderStr, Class requiredType, Boolean isDealOne2Many, Object... args){
		Class rt_class = requiredType;
		if(rt_class.isAnnotationPresent(AP_MODEL.class)){
			StringBuilder sql_count = new StringBuilder();
			StringBuilder sql = new StringBuilder();
			
			sql_count.append(countSQLStr).append(" ");
			sql.append(sqlStr).append(" ");
			
			if(!Str.isEmpty(whereStr)){
				sql.append("WHERE ").append(whereStr);
				sql_count.append("WHERE ").append(whereStr);
			}
			Integer count = jminiJDBCTemplate.queryForObject(sql_count.toString(), args, Integer.class);
			p.setMaxRows(count);
			List<Object> argslist = new ArrayList<Object>();
			if(args != null){
				argslist.addAll(Arrays.asList(args));
			}
			argslist.add(p.calculateMaxRecord());
			argslist.add(p.getPageSize());
			if(!Str.isEmpty(orderStr)){
				sql.append(" ORDER BY ").append(orderStr);
			}
			sql.append(" LIMIT ? , ? ");
			List<Module> models = jminiJDBCTemplate.query(sql.toString() , argslist.toArray(), new ApModelRowMapper(requiredType, this, isDealOne2Many));
			p.setDatas(models);
		}
	}
	
	//使用sql统计数据
	public Integer countData(String countSQLStr, Object... args){
		Integer count = null;
		count = jminiJDBCTemplate.queryForObject(countSQLStr, args, Integer.class);
		return count;
	}
	
	//使用sql求和数据
	public Double sumData(String countSQLStr, Object... args){
		return jminiJDBCTemplate.queryForObject(countSQLStr, args, Double.class);
	}
	
	/**
	 * 使用sql更新数据
	 * @param sql SQL语句
	 * @param args 参数
	 * @return
	 */
	public int updateBySQL(String sql, Object... args){
		return jminiJDBCTemplate.update(sql, args);
	}

}
